package com.zjut.zs.excel.manager;

import java.io.File;
import java.io.FileInputStream;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.axis.client.async.Status;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.zjut.zs.applyview.dao.ZsApplyView;
import com.zjut.zs.applyview.manager.ZsApplyViewManager;
import com.zjut.zs.coname.dao.ZsCoName;
import com.zjut.zs.coname.dao.ZsCoNameDAO;
import com.zjut.zs.coname.manager.ZsCoNameManager;
import com.zjut.zs.confirm.dao.ZsConfirm;
import com.zjut.zs.confirm.manager.ZsConfirmManager;
import com.zjut.zs.user.dao.ZsUser;
import com.zjut.zs.user.manager.ZsUserManager;
import com.zjut.zs.userstatus.dao.ZsUserStatus;
import com.zjut.zs.userstatus.dao.ZsUserStatusDAO;
import com.zjut.zs.userstatus.manager.ZsUserStatusManager;
import com.zjut.zs.xy.dao.ZsXy;
import com.zjut.zs.xy.manager.ZsXyManager;

public class ZsExcelManager {
	@Resource(name = "ZsUserStatusDAO")
	private ZsUserStatusDAO usDAO;
	
	@Resource(name = "ZsUserStatusManager")
	private ZsUserStatusManager usManager;
	
	@Resource(name = "ZsApplyViewManager")
	private ZsApplyViewManager avManager;

	@Resource(name = "ZsXyManager")
	private ZsXyManager xManager;
	
	@Resource(name = "ZsUserManager")
	private ZsUserManager uManager;
	
	@Resource(name = "ZsCoNameManager")
	private ZsCoNameManager coManager;
	
	@Resource(name = "ZsConfirmManager")
	private ZsConfirmManager cManager;

	@Resource(name = "ZsCoNameDAO")
	private ZsCoNameDAO zsColNameDAO;

	private Map acColumnnameMap;


	public Workbook ExcelRead(String path) throws Exception {
		Workbook wb = null;
		File f = new File(path);
		FileInputStream is = new FileInputStream(f);
		String fileName = f.getName();
		String prefix = fileName.substring(fileName.lastIndexOf(".") + 1);
		if ("XLS".equals(String.valueOf(prefix).toUpperCase())) {// 2003
			wb = new HSSFWorkbook(is);
		} else if ("XLSX".equals(String.valueOf(prefix).toUpperCase())) {// 2007
			wb = new XSSFWorkbook(is);
		}
		return wb;
	}

	public boolean isExcelData(String path) throws Exception {
		// 判断Excel是否有数据，或数据量超过5000
		Workbook wb = ExcelRead(path);
		Sheet sheet = wb.getSheetAt(0);
		int rownum = sheet.getLastRowNum();
		if (rownum < 1 || rownum>20000) {
			return false;
		}
		return true;
	}

	public List<String> ExcelColumn(String path) throws Exception {
		Workbook wb = ExcelRead(path);
		List<String> excelColumn = new ArrayList<String>();
		Sheet sheet = wb.getSheetAt(0);
		Row row = sheet.getRow(0);
		Cell cell = null;
		for (int i = 0; i < row.getLastCellNum(); i++) {
			cell = row.getCell(i);
			excelColumn.add(cell.getRichStringCellValue().getString().trim());
		}
		return excelColumn;
	}
	
	public String eqExcleColumn(String path, List<String> inputColumn) throws Exception{
		List<String> excelColumn=ExcelColumn(path);
		String rString="";
		if (excelColumn.size()!=inputColumn.size()) {
			rString="字段的个数"+excelColumn.size()+"同指定个数"+inputColumn.size()+"不符合，请检查是否正确";
			return rString;
		}
		for (int i = 0; i < inputColumn.size(); i++) {
			if (!inputColumn.get(i).equals(excelColumn.get(i))) {
				rString=rString+inputColumn.get(i)+" ";
			}
		}
		return rString;
	}

	public List<ZsUserStatus> ExcelData(String path, List<String> inputColumn)
			throws Exception {
		Workbook wb = ExcelRead(path);
		List<ZsUserStatus> acList = new ArrayList<ZsUserStatus>();
		Row row = null;
		String enString = "";
		Class objClass = Class.forName("com.zjut.zs.userstatus.dao.ZsUserStatus");
		Sheet sheet = wb.getSheetAt(0);
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		
		acColumnnameMap = new HashMap();
		List colist = coManager.getDatasForUp();
		for(int i = 0; i<colist.size(); i++) {
			ZsCoName co = (ZsCoName)colist.get(i);
			acColumnnameMap.put(co.getCoCn(), co);
		}
		
		for (int i = 1; i <= rowNum; i++) {
			ZsUserStatus userstatus = new ZsUserStatus();
			row = sheet.getRow(i);
			int j = 0;
			int k = 0;
			while (j < colNum) {
				
				if (null!=row.getCell((short) j)) {					
					try {
						enString = CnToEnUP(inputColumn.get(k).toString());
						System.out.println("*********"+"set " + enString);
						if ("ExId".equals(enString)) {
							Method setPropertyMethod = objClass.getMethod("set" + enString, Integer.class);
							setPropertyMethod.invoke(userstatus, Integer.parseInt(getCellFormatValue(row.getCell((short) j)).toString().trim()));
						} else {
							Method setPropertyMethod = objClass.getMethod("set" + enString, String.class);
							setPropertyMethod.invoke(userstatus, getCellFormatValue(row.getCell((short) j)).toString().trim());
						}		
						System.out.println("####-"+getCellFormatValue(row.getCell((short) j)).toString().trim());
					} catch (Exception e) {
						//e.printStackTrace();
						//不存在，略过
					}					
				}
				j++;
				k++;
			}
			acList.add(userstatus);
		}
		return acList;
	}

	public List<ZsUserStatus> ExcelData2(String path, List<String> inputColumn)	throws Exception {//通过报名编号进行导入，待废除
		Workbook wb = ExcelRead(path);
		List<ZsUserStatus> acList = new ArrayList<ZsUserStatus>();
		Row row = null;
		Sheet sheet = wb.getSheetAt(0);
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		
		for (int i = 1; i <= rowNum; i++) {
			ZsUserStatus userstatus = new ZsUserStatus();			
			row = sheet.getRow(i);
			try {
				String usIds = getCellFormatValue(row.getCell(1)).toString().trim();
				//System.out.println("!!!-"+usIds);
				//System.out.println("!!!-"+usIds.substring(4, 9));
				String usId = usIds.substring((usIds.length()-5), usIds.length());
				//System.out.println("!!!-"+usId);
				//System.out.println("!!!-"+uManager.getData(usId).getUsIdcard());
				System.out.println("！！！！！！！！！！ id "+usId);
				userstatus.setUsIdcard(uManager.getData(usId).getUsIdcard());
				userstatus.setPayStatus(getCellFormatValue(row.getCell(2)).toString().trim());
				System.out.println("!!!-添加"+i+"条数据"+",id "+usId+ ",身份证"+userstatus.getUsIdcard()+" 缴费状态"+userstatus.getPayStatus());
				
			} catch (Exception e) {
				e.printStackTrace();
				//不存在，略过
			}	
			
			acList.add(userstatus);
		}
		return acList;
	}

	public List<ZsConfirm> ExcelData3(String path, List<String> inputColumn)	throws Exception {//导入教育厅的会考成绩
		Workbook wb = ExcelRead(path);
		List<ZsConfirm> cList = new ArrayList<ZsConfirm>();
		Row row = null;
		Sheet sheet = wb.getSheetAt(0);
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		
		for (int i = 1; i <= rowNum; i++) {
			ZsConfirm confirm = new ZsConfirm();			
			row = sheet.getRow(i);
			try {
				
				confirm.setUsName(getCellFormatValue(row.getCell(0)).toString().trim());
				confirm.setUsIdcard(getCellFormatValue(row.getCell(1)).toString().trim());
				confirm.setCYw(getCellFormatValue(row.getCell(2)).toString().trim());
				confirm.setCSx(getCellFormatValue(row.getCell(3)).toString().trim());
				confirm.setCWy(getCellFormatValue(row.getCell(4)).toString().trim());
				confirm.setCZz(getCellFormatValue(row.getCell(5)).toString().trim());
				confirm.setCLs(getCellFormatValue(row.getCell(6)).toString().trim());
				confirm.setCDl(getCellFormatValue(row.getCell(7)).toString().trim());
				confirm.setCWl(getCellFormatValue(row.getCell(8)).toString().trim());
				confirm.setCHx(getCellFormatValue(row.getCell(9)).toString().trim());
				confirm.setCSw(getCellFormatValue(row.getCell(10)).toString().trim());
				confirm.setCJs(getCellFormatValue(row.getCell(11)).toString().trim());
				confirm.setCZxzh1(getCellFormatValue(row.getCell(12)).toString().trim());
				confirm.setCZxzh2(getCellFormatValue(row.getCell(13)).toString().trim());
				confirm.setCZxzh3(getCellFormatValue(row.getCell(14)).toString().trim());
				confirm.setCZxzh4(getCellFormatValue(row.getCell(15)).toString().trim());
				
				System.out.println("!!!-添加"+i+"条数据"+",身份证"+confirm.getUsIdcard());
				
			} catch (Exception e) {
				e.printStackTrace();
				//不存在，略过
			}	
			cList.add(confirm);
		}
		return cList;
	}

	public List<ZsUser> ExcelData4(String path, List<String> inputColumn)	throws Exception {//导入教育厅的报考学生信息
		Workbook wb = ExcelRead(path);
		List<ZsUser> list = new ArrayList<ZsUser>();
		Row row = null;
		Sheet sheet = wb.getSheetAt(0);
		int rowNum = sheet.getLastRowNum();
		row = sheet.getRow(0);
		int colNum = row.getPhysicalNumberOfCells();
		
		for (int i = 1; i <= rowNum; i++) {
			ZsUser obj = new ZsUser();			
			row = sheet.getRow(i);
			try {
				obj.setUsName(getCellFormatValue(row.getCell(0)).toString().trim());
				obj.setUsIdcard(getCellFormatValue(row.getCell(1)).toString().trim());
				obj.setUsMobile(getCellFormatValue(row.getCell(2)).toString().trim());
				obj.setUsName(getCellFormatValue(row.getCell(0)).toString().trim());
				
				System.out.println("!!!-添加"+i+"条数据"+",身份证"+obj.getUsIdcard());
				
			} catch (Exception e) {
				e.printStackTrace();
				//不存在，略过
			}	
			list.add(obj);
		}
		return list;
	}
	
	public String update(List<ZsUserStatus> usList) {
		int num=0;
		int nullid=0;
		String msg = "";
		System.out.println("******************************需要更新"+usList.size());
		for (ZsUserStatus us : usList) {
			if (!isEmpty(us.getUsIdcard())) {//身份证必须非空
				ZsUserStatus obj = new ZsUserStatus();
				obj.setUsIdcard(us.getUsIdcard());
				System.out.println("******************************"+us.getUsIdcard()+" "+us.getScColumn1());
				List list = usManager.getDataByInstance(1, 1, null, null, obj).getObjects();
				if (list.size()>0) {
					System.out.println("******************************"+list.size());
					obj = (ZsUserStatus)list.get(0);
					if (!isEmpty(us.getCheckStatus())) {
						obj.setCheckStatus(us.getCheckStatus());
					}
					if (!isEmpty(us.getCheckRemark())) {
						obj.setCheckRemark(us.getCheckRemark());
					}
					if (!isEmpty(us.getTestNo())) {
						obj.setTestNo(us.getTestNo());
					}
					if (!isEmpty(us.getTestNum())) {
						obj.setTestNum(us.getTestNum());
					}
					if (!isEmpty(us.getTestMnum())) {
						obj.setTestMnum(us.getTestMnum());
					}
					if (!isEmpty(us.getTestBnum())) {   
						obj.setTestBnum(us.getTestBnum());
					}
					if (!isEmpty(us.getPayStatus())) {
						obj.setPayStatus(us.getPayStatus());
					}
					if (!isEmpty(us.getPassStatus())) {
						obj.setPassStatus(us.getPassStatus());
					}
					if (!isEmpty(us.getPassRemark())) {
						obj.setPassRemark(us.getPassRemark());
					}
					if (!isEmpty(us.getEmsStatus())) {
						obj.setEmsStatus(us.getEmsStatus());
					}
					if (!isEmpty(us.getScColumn1())) {
						obj.setScColumn1(us.getScColumn1());
					}
					if (!isEmpty(us.getScColumn2())) {
						obj.setScColumn2(us.getScColumn2());
					}
					if (!isEmpty(us.getScColumn3())) {
						obj.setScColumn3(us.getScColumn3());
					}
					if (!isEmpty(us.getScColumn4())) {
						obj.setScColumn4(us.getScColumn4());
					}
					if (!isEmpty(us.getScColumn5())) {
						obj.setScColumn5(us.getScColumn5());
					}
					if (!isEmpty(us.getScColumn6())) {
						obj.setScColumn6(us.getScColumn6());
					}
					if (!isEmpty(us.getScColumn7())) {
						obj.setScColumn7(us.getScColumn7());
					}
					if (!isEmpty(us.getScColumn8())) {
						obj.setScColumn8(us.getScColumn8());
					}
					if (!isEmpty(us.getScColumn9())) {
						obj.setScColumn9(us.getScColumn9());
					}
					if (!isEmpty(us.getScColumn10())) {
						obj.setScColumn10(us.getScColumn10());
					}
					if (null != us.getExId()) {
						obj.setExId(us.getExId());
					}
					usManager.updateData(obj);//修改excel字段至数据库
					num++;//更新数量加1
				} else {
					msg+="号码"+us.getUsIdcard()+"未找到,";
				}
			}else{
				nullid++;//身份证号码为空
				System.out.println("******************************身份证为空："+us.getUsIdcard());
			}
		}
		return "更新了"+num+"条数据,"+nullid+"条身份证为空,"+msg;
	}
	
	public String updateConfirm(List<ZsConfirm> cList) {
		int num=0;
		int nullid=0;
		
		System.out.println("******************************需要更新"+cList.size());
		
		for (ZsConfirm confirm : cList) {
			cManager.addData(confirm);
			num++;
		}
		return "导入了"+num+"条数据,"+nullid+"条身份证为空,";
	}
	
	
	private boolean isEmpty(String value) {
		if (value==null || value.trim().length()==0) {
			return true;
		}
		return false;
	}

	public String getCellFormatValue(Cell cell) {
		// 获取excle单元格的值转化为String
		String cellvalue = "";
		if (cell != null) {
			// 判断当前Cell的Type
			switch (cell.getCellType()) {
			case Cell.CELL_TYPE_NUMERIC: {
				DecimalFormat df = new DecimalFormat("0");  
				cellvalue = df.format(cell.getNumericCellValue()); 
			}
			case Cell.CELL_TYPE_FORMULA: {
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					Date date = cell.getDateCellValue();
					SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
					cellvalue = sdf.format(date);

				} else {
					DecimalFormat df = new DecimalFormat("0");  					  
					cellvalue = df.format(cell.getNumericCellValue());  
					
				}
				break;
			}
			case Cell.CELL_TYPE_STRING:
				cellvalue = cell.getRichStringCellValue().getString();
				break;
			default:
				cellvalue = " ";
			}
		} else {
			cellvalue = "";
		}
		return cellvalue;
	}

	private String CnToEnUP(String cn) {
		ZsCoName co = (ZsCoName) acColumnnameMap.get(cn);
		StringBuffer sb = new StringBuffer();
		if (co!=null) {
			System.out.println("*********"+co.getCoEn());
			sb = new StringBuffer(co.getCoEn());
			sb.setCharAt(0, Character.toUpperCase(sb.charAt(0)));
		}
		
		return sb.toString();
	}

}
